﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using VAdvantage.Framework;
using VAdvantage.Controls;
using VAdvantage.Utility;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using VAdvantage.DataBase;
using VAdvantage.Logging;
using System.Data;
using System.Threading;
using System.Linq;
using VAdvantage.Classes;
using System.Text.RegularExpressions;

namespace VAdvantage.Search
{
    public class InfoGeneral : Info
    {
        #region PrivateVariables
        string _queryValue = "";
        private VLabel label1 = new VLabel();
        private const int HEIGHT = 23;
        private VTextBox textField1 = new VTextBox(HEIGHT);
        private VLabel label2 = new VLabel();
        private VTextBox textField2 = new VTextBox(HEIGHT);
        private VLabel label3 = new VLabel();
        private VTextBox textField3 = new VTextBox(HEIGHT);
        private VLabel label4 = new VLabel();
        private VTextBox textField4 = new VTextBox(HEIGHT);

        private List<String> _QueryColumns = new List<String>();
        private Info_Column[] _GeneralLayout;

        private static readonly Regex PropertNameRegex =
              new Regex(@"^[A-Za-z_]+[A-Za-z0-9' ']*$", RegexOptions.Singleline);


        #endregion

        public InfoGeneral(Object frame, bool modal, int WindowNo, String value, String tableName, String keyColumn, bool multiSelection, String whereClause)
            : base(frame, modal, WindowNo, tableName, keyColumn, multiSelection, whereClause)
        {
            base.SetTitle(Msg.GetMsg(Envs.GetContext(), "Info", true));

            _queryValue = value;
            StartInit();
            InitInfo();
        }

        private void StartInit()
        {
            InfoSetting();

            label1.VerticalAlignment = System.Windows.VerticalAlignment.Bottom;
            label1.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
            label1.Margin = new Thickness(0, 0, 5, 0);

            textField1.Margin = new Thickness(0, 0, 5, 0);
            textField1.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
            

            label2.VerticalAlignment = System.Windows.VerticalAlignment.Bottom;
            label2.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
            label2.Margin = new Thickness(0, 0, 5, 0);

            textField2.Margin = new Thickness(0, 0, 5, 0);
            textField2.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;

            label3.VerticalAlignment = System.Windows.VerticalAlignment.Bottom;
            label3.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
            label3.Margin = new Thickness(0, 0, 5, 0);

            textField3.Margin = new Thickness(0, 0, 5, 0);
            textField3.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;

            label4.VerticalAlignment = System.Windows.VerticalAlignment.Bottom;
            label4.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
            label4.Margin = new Thickness(0, 0, 5, 0);

            textField4.Margin = new Thickness(0, 0, 5, 0);
            textField4.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;

            label1.Content = Msg.GetMsg(Utility.Envs.GetContext(), "Value");
            label2.Content = Msg.GetMsg(Utility.Envs.GetContext(), "Name");
            label3.Content = Msg.GetMsg(Utility.Envs.GetContext(), "Contact");
            label4.Content = Msg.GetMsg(Utility.Envs.GetContext(), "EMail");
            textField1.Width = 120;
            textField2.Width = 120;
            textField3.Width = 120;
            textField4.Width = 120;

            //#Line 1
            Grid.SetRow(label1, 0);
            Grid.SetColumn(label1, 0);
            this.tablePanel.Children.Add(label1);

            Grid.SetRow(label2, 0);
            Grid.SetColumn(label2, 1);
            this.tablePanel.Children.Add(label2);

            Grid.SetRow(label3, 0);
            Grid.SetColumn(label3, 2);
            this.tablePanel.Children.Add(label3);

            Grid.SetRow(label4, 0);
            Grid.SetColumn(label4, 3);
            this.tablePanel.Children.Add(label4);


            //#line 2
            Grid.SetRow(textField1, 1);
            Grid.SetColumn(textField1, 0);
            this.tablePanel.Children.Add(textField1);


            Grid.SetRow(textField2, 1);
            Grid.SetColumn(textField2, 1);
            this.tablePanel.Children.Add(textField2);

            Grid.SetRow(textField3, 1);
            Grid.SetColumn(textField3, 2);
            this.tablePanel.Children.Add(textField3);

            Grid.SetRow(textField4, 1);
            Grid.SetColumn(textField4, 3);
            this.tablePanel.Children.Add(textField4);



        }

        public void InitInfo()
        {
            SetBusy(true);
                
            System.Threading.ThreadPool.QueueUserWorkItem(delegate
            {
                if (!InitInfoTable())
                {
                    System.Windows.Deployment.Current.Dispatcher.BeginInvoke(()=>SetBusy(false));
                    return;
                }

                System.Windows.Deployment.Current.Dispatcher.BeginInvoke(() => InvoKFunction());
            });
        }


        private void InvoKFunction()
        {
            //  prepare table
            StringBuilder where = new StringBuilder("IsActive='Y'");
            if (_whereClause.Length > 0)
            {
                where.Append(" AND ").Append(_whereClause);
            }
            PrepareTable(_tableName, where.ToString(), "2");

            //	Set & enable Fields
            label1.Content = Msg.Translate(Envs.GetContext(), _QueryColumns[0]);
            textField1.KeyDown += new KeyEventHandler(fieldValue_KeyDown);
            textField2.KeyDown += new KeyEventHandler(fieldValue_KeyDown);
            textField3.KeyDown += new KeyEventHandler(fieldValue_KeyDown);
            textField4.KeyDown += new KeyEventHandler(fieldValue_KeyDown);
            vdgvRecords.AutoGeneratingColumn += new EventHandler<DataGridAutoGeneratingColumnEventArgs>(vdgvRecords_AutoGeneratingColumn);
            vdgvRecords.BindingValidationError += new EventHandler<ValidationErrorEventArgs>(vdgvRecords_BindingValidationError);
            if (_QueryColumns.Count > 1)
            {
                label2.Content = Msg.Translate(Utility.Envs.GetContext(), _QueryColumns[1].ToString());
            }
            else
            {
                label2.Visible = false;
            }
            if (_QueryColumns.Count > 2)
            {
                label3.Content = Msg.Translate(Utility.Envs.GetContext(), _QueryColumns[2].ToString());
            }
            else
            {
                //tablePanel.ColumnDefinitions.RemoveAt(2);//uncomment it
                //GetControlFromPosition(0, 2);//remove label3
                ((Label)GetControlFromPosition(0, 2)).Visibility = System.Windows.Visibility.Collapsed;
                //GetControlFromPosition(1, 2);//remove textField3
                ((VTextBox)GetControlFromPosition(1, 2)).Visibility = System.Windows.Visibility.Collapsed;
            }
            if (_QueryColumns.Count > 3)
            {
                label4.Content = Msg.Translate(Utility.Envs.GetContext(), _QueryColumns[3].ToString());
            }
            else
            {
                //GetControlFromPosition(0, 3);//remove label4
                ((Label)GetControlFromPosition(0, 3)).Visibility = System.Windows.Visibility.Collapsed;

                //GetControlFromPosition(1, 3);//remove textField4
                ((VTextBox)GetControlFromPosition(1, 3)).Visibility = System.Windows.Visibility.Collapsed;
            }


            var count = 0;
            if (vdgvRecords.ItemsSource != null)
            {
                count = vdgvRecords.ItemsSource.OfType<object>().Count();
            }
            base.SetStatusMsg(count.ToString() + " " + Msg.GetMsg(Envs.GetContext(), "SearchRows_EnterQuery", true));


            SetBusy(false);

            textField1.Text = _queryValue;        // set the deafult value
            textField1.Focus();             // set the focus on first textfield
            if (_queryValue != null && _queryValue.Length > 0)
            {
                ExecuteQuery();
            }

            _LoadedOK = true;
        }

        void vdgvRecords_BindingValidationError(object sender, ValidationErrorEventArgs e)
        {
            
        }

        void vdgvRecords_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
        {
            foreach (DataGridColumn col in vdgvRecords.Columns)
            {
                if (col.DisplayIndex == 0)
                {
                    col.Visibility = Visibility.Collapsed;
                }
            }
        }

        /// <summary>
        /// Remove child control from positions
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private IControl GetControlFromPosition(int row, int col)
        {
            IControl crtlObj = null;
            /*****Get control form specified column and row from Grid***********/
            var eList = from child in tablePanel.Children
                        where Grid.GetRow((FrameworkElement)child) == row && Grid.GetColumn((FrameworkElement)child) == col
                        select child;

            //Remove any elements in the list
            while (eList.Count() > 0)
            {
                crtlObj = (IControl)eList.First();
                break;
            }
            /**********************************/
            return crtlObj;
        }

        /// <summary>
        /// Init info with Table.
        /// - find QueryColumns (Value, Name, ..)
        /// - build gridController & column
        /// </summary>
        /// <returns>true if success</returns>
        private bool InitInfoTable()
        {
            bool blStatus = false;

            Context ctx = Utility.Envs.GetContext();
            //	Get Query Columns -------------------------------------------------
            String sql = "SELECT c.ColumnName, t.AD_Table_ID, t.TableName "
                + "FROM AD_Table t"
                + " INNER JOIN AD_Column c ON (t.AD_Table_ID=c.AD_Table_ID)"
                + "WHERE c.AD_Reference_ID=10"
               + " AND t.TableName=@tablename"	//	#1

                //	Displayed in Window
                + " AND EXISTS (SELECT * FROM AD_Field f "
                    + "WHERE f.AD_Column_ID=c.AD_Column_ID"
                    + " AND f.IsDisplayed='Y' AND f.IsEncrypted='N' AND f.ObscureType IS NULL) "
                //
                + "ORDER BY c.IsIdentifier DESC, c.SeqNo";
            int AD_Table_ID = 0;
            String tableName = null;
            IDataReader dr = null;
            try
            {
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@tablename", _tableName);

                dr = DB.ExecuteReader(sql, param);
                //dr = DB.ExecuteReader(sql, null);
                while (dr.Read())
                {
                    _QueryColumns.Add(dr[0].ToString());
                    if (AD_Table_ID == 0)
                    {
                        AD_Table_ID = Utility.Util.GetValueOfInt(dr[1].ToString());
                        tableName = dr[2].ToString();
                    }
                }
                dr.Close();

            }
            catch (Exception e)
            {
                if (dr != null)
                {
                    dr.Close();
                }
                log.Log(Level.SEVERE, sql, e);
                //ErrorLog.FillErrorLog("InfoGereral.InitInfoTable()", sql, e.Message, VAdvantage.Framework.Message.MessageType.ERROR);
                blStatus = false;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }
            //	Minimum check
            if (_QueryColumns.Count == 0)
            {
                log.Log(Level.SEVERE, "No query columns found");
                //ErrorLog.FillErrorLog("InfoGereral.InitInfoTable()", "No query columns found", "", VAdvantage.Framework.Message.MessageType.ERROR);

                blStatus = false;
            }
           // log.Finest("Table " + tableName + ", ID=" + AD_Table_ID  + ", QueryColumns #" + m_queryColumns.size());
            //	Only 4 Query Columns
            while (_QueryColumns.Count > 4)
                _QueryColumns.Remove(_QueryColumns[_QueryColumns.Count - 1].ToString());
            //  Set Title
            String title = Msg.Translate(ctx, tableName + "_ID");  //  best bet
            if (title.EndsWith("_ID"))
                title = Msg.Translate(ctx, tableName);             //  second best bet
            //setTitle(getTitle() + " " + title);


            //	Get Display Columns -----------------------------------------------
            List<Info_Column> list = new List<Info_Column>();
            sql = "SELECT c.ColumnName, c.AD_Reference_ID, c.IsKey, f.IsDisplayed, c.AD_Reference_Value_ID "
                + "FROM AD_Column c"
                + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID)"
                + " INNER JOIN AD_Tab tab ON (t.AD_Window_ID=tab.AD_Window_ID)"
                + " INNER JOIN AD_Field f ON (tab.AD_Tab_ID=f.AD_Tab_ID AND f.AD_Column_ID=c.AD_Column_ID) "
                + "WHERE t.AD_Table_ID=@tablename "
                + " AND (c.IsKey='Y' OR "
                //	+ " (f.IsDisplayed='Y' AND f.IsEncrypted='N' AND f.ObscureType IS NULL)) "
                    + " (f.IsEncrypted='N' AND f.ObscureType IS NULL)) "
                + "ORDER BY c.IsKey DESC, f.SeqNo";
            //IDataReader dr = null;
            try
            {
                //execute sql to generate query columns
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@tablename", AD_Table_ID);

                dr = DB.ExecuteReader(sql, param);
                while (dr.Read())
                {
                    String columnName = dr[0].ToString();
                    int displayType = Utility.Util.GetValueOfInt(dr[1].ToString());
                    bool IsKey = dr[2].ToString().Equals("Y");
                    bool IsDisplayed = dr[3].ToString().Equals("Y");
                    int AD_Reference_Value_ID = dr[4].ToString() == "" ? int.Parse("0") : int.Parse(dr[4].ToString());
                    //  Default
                    StringBuilder colSql = new StringBuilder(columnName);
                    Type colClass = null;
                    //
                    if (IsKey)
                        colClass = typeof(IDColumn);
                    else if (!IsDisplayed)
                    {
                    }
                    else if (displayType == DisplayType.YesNo)
                        colClass = typeof(Boolean);
                    else if (displayType == DisplayType.Amount)
                        colClass = typeof(Decimal);
                    else if (displayType == DisplayType.Number || displayType == DisplayType.Quantity)
                        colClass = typeof(Double);
                    else if (displayType == DisplayType.Integer)
                        colClass = typeof(Int32);
                    else if (displayType == DisplayType.String || displayType == DisplayType.Text || displayType == DisplayType.Memo)
                        colClass = typeof(String);
                    else if (DisplayType.IsDate(displayType))
                        colClass = typeof(DateTime);
                    //  ignore Binary, Button, ID, RowID
                    //	else if (displayType == DisplayType.Account)
                    //	else if (displayType == DisplayType.Location)
                    //	else if (displayType == DisplayType.Locator)
                    else if (displayType == DisplayType.List)
                    {
                        if (Utility.Envs.IsBaseLanguage(ctx, ""))//GlobalVariable.IsBaseLanguage())
                            colSql = new StringBuilder("(SELECT l.Name FROM AD_Ref_List l WHERE l.AD_Reference_ID=")
                                .Append(AD_Reference_Value_ID).Append(" AND l.Value=").Append(columnName)
                                .Append(") AS ").Append(columnName);
                        else
                            colSql = new StringBuilder("(SELECT t.Name FROM AD_Ref_List l, AD_Ref_List_Trl t "
                                + "WHERE l.AD_Ref_List_ID=t.AD_Ref_List_ID AND l.AD_Reference_ID=")
                                .Append(AD_Reference_Value_ID).Append(" AND l.Value=").Append(columnName)
                                .Append(" AND t.AD_Language='").Append(Utility.Envs.GetAD_Language(ctx))
                                .Append("') AS ").Append(columnName);
                        colClass = typeof(String);
                    }
                    //	else if (displayType == DisplayType.Table)
                    //	else if (displayType == DisplayType.TableDir || displayType == DisplayType.Search)

                    if (colClass != null)
                    {
                        string cName = Msg.Translate(ctx, columnName);


                        if (!PropertNameRegex.IsMatch(cName))
                        {
                            cName = columnName;
                        }

                        list.Add(new Info_Column(cName, colSql.ToString().ToUpper(), colClass));
                        log.Finest("Added Column=" + columnName);
                    }
                    else
                    {
                        log.Finest("Not Added Column=" + columnName);
                    }

                }
                dr.Close();

            }
            catch (Exception e)
            {
                if (dr != null)
                {
                    dr.Close();
                }
                log.Log(Level.SEVERE, sql, e);
                blStatus = false;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            if (list.Count == 0)
            {
                //ShowMessage.Error("Error", true, "No Info Columns");
                log.Log(Level.SEVERE, "No Info for AD_Table_ID=" + AD_Table_ID + " - " + sql);
                blStatus = false;
            }
            //log.finest("InfoColumns #" + list.size()); 

            //  Convert ArrayList to Array
            _GeneralLayout = new Info_Column[list.Count];
            _GeneralLayout = list.ToArray();

            blStatus = true;


            return blStatus;
        }	//	initInfoTable


        /// <summary>
        /// Construct SQL Where Clause and define parameters.
        /// (setParameters needs to set parameters)
        /// Includes first AND
        /// </summary>
        /// <returns>where clause</returns>
        public override String GetSQLWhere()
        {
            Dispatcher.BeginInvoke(delegate() { textValue1 = textField1.Text; });
            Dispatcher.BeginInvoke(delegate() { textValue2 = textField2.Text; });
            Dispatcher.BeginInvoke(delegate() { textValue3 = textField3.Text; });
            Dispatcher.BeginInvoke(delegate() { textValue4 = textField4.Text; });

            StringBuilder sql = new StringBuilder();
            AddSQLWhere(sql, 0, textValue1.ToUpper());
            AddSQLWhere(sql, 1, textValue2.ToUpper());
            AddSQLWhere(sql, 2, textValue3.ToUpper());
            AddSQLWhere(sql, 3, textValue4.ToUpper());
            return sql.ToString();
        }


        string textValue1 = "";
        string textValue2 = "";
        string textValue3 = "";
        string textValue4 = "";

        private void SetValuesOfControl()
        {
            textValue1 = textField1.Text;
            textValue2 = textField2.Text;
            textValue3 = textField3.Text;
            textValue4 = textField4.Text;
        }

        /// <summary>
        /// Add directly Query as Strings
        /// </summary>
        /// <param name="sql">sql buffer</param>
        /// <param name="index">index</param>
        /// <param name="value">value</param>
        private void AddSQLWhere(StringBuilder sql, int index, String value)
        {
            if (!(value.Equals("") || value.Equals("%")) && index < _QueryColumns.Count)
            {
                sql.Append(" AND UPPER(").Append(_QueryColumns[index].ToString()).Append(") LIKE '");
                sql.Append(value);
                if (value.EndsWith("%"))
                    sql.Append("'");
                else
                    sql.Append("%'");
            }
        }

        /// <summary>
        /// Set Parameters for Query.
        /// (as defined in getSQLWhere)
        /// </summary>
        /// <param name="pstmt">statement</param>
        /// <param name="forCount">for counting records</param>
        public override void SetParameters(out SqlParameter[] param, bool forCount)
        {
            param = null;
            return;
        }   //  setParameters

        void fieldValue_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Enter)
            //ExecuteQuery();
            {
                Search();
            }
        }

        void Search()
        {
            ExecuteQuery();
        }

        protected override void bRefresh_Click(object sender, RoutedEventArgs e)
        {
            //base.bRefresh_Click(sender, e);
            Search();
        }


        /// <summary>
        /// Sets the display setting
        /// </summary>
        protected void InfoSetting()
        {
            RowDefinition row = new RowDefinition();
            row.Height = new GridLength(0, GridUnitType.Auto);
            row.MinHeight = 30;
            this.tablePanel.RowDefinitions.Add(row);

            row = new RowDefinition();
            row.Height = new GridLength(0, GridUnitType.Auto);
            row.MinHeight = 30;
            this.tablePanel.RowDefinitions.Add(row);


            ColumnDefinition column = new ColumnDefinition();
            column.Width = new GridLength(25, GridUnitType.Star);
            this.tablePanel.ColumnDefinitions.Add(column);

            column = new ColumnDefinition();
            column.Width = new GridLength(45, GridUnitType.Star);
            this.tablePanel.ColumnDefinitions.Add(column);

            column = new ColumnDefinition();
            column.Width = new GridLength(25, GridUnitType.Star);
            this.tablePanel.ColumnDefinitions.Add(column);

            column = new ColumnDefinition();
            column.Width = new GridLength(45, GridUnitType.Star);
            this.tablePanel.ColumnDefinitions.Add(column);

        }

        /// <summary>
        /// Get Layout
        /// </summary>
        /// <returns>array of Column_Info</returns>
        protected override Info_Column[] GetInfoColumns()
        {
            return _GeneralLayout;
        }
        


    }
}
